package org.example.managere.service.impl;

import org.example.managere.service.DfcmDataService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Service;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * DFCM数据库通用服务实现
 */
@Service
public class DfcmDataServiceImpl implements DfcmDataService {

    private static final String DATABASE_NAME = "dfcmdata";
    
    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Override
    public List<String> getAllTables() {
        String sql = "SHOW TABLES FROM " + DATABASE_NAME;
        return jdbcTemplate.queryForList(sql, String.class);
    }

    @Override
    public Map<String, String> getTableColumns(String tableName) {
        try {
            Map<String, String> columns = new LinkedHashMap<>();
            
            // 使用元数据获取列信息
            jdbcTemplate.query(
                "SELECT * FROM " + DATABASE_NAME + "." + escapeTableName(tableName) + " LIMIT 0",
                (ResultSetExtractor<Void>) rs -> {
                    ResultSetMetaData metaData = rs.getMetaData();
                    int columnCount = metaData.getColumnCount();
                    
                    for (int i = 1; i <= columnCount; i++) {
                        String columnName = metaData.getColumnName(i);
                        String columnType = metaData.getColumnTypeName(i);
                        columns.put(columnName, columnType);
                    }
                    return null;
                }
            );
            
            return columns;
        } catch (Exception e) {
            throw new RuntimeException("获取表列信息失败: " + e.getMessage(), e);
        }
    }

    @Override
    public Map<String, Object> getTableData(String tableName, int page, int size, 
                                          String sortField, String sortDirection, String keyword) {
        Map<String, Object> result = new HashMap<>();
        
        // 构建基本查询
        StringBuilder sql = new StringBuilder("SELECT * FROM ")
                .append(DATABASE_NAME).append(".").append(escapeTableName(tableName));
        
        // 构建条件子句
        List<Object> params = new ArrayList<>();
        if (keyword != null && !keyword.isEmpty()) {
            sql.append(" WHERE ");
            
            // 获取表的所有列，用于构建LIKE条件
            Map<String, String> columns = getTableColumns(tableName);
            List<String> likeConditions = new ArrayList<>();
            
            for (String columnName : columns.keySet()) {
                likeConditions.add(columnName + " LIKE ?");
                params.add("%" + keyword + "%");
            }
            
            sql.append(String.join(" OR ", likeConditions));
        }
        
        // 添加排序
        if (sortField != null && !sortField.isEmpty()) {
            sql.append(" ORDER BY ").append(sortField);
            if ("desc".equalsIgnoreCase(sortDirection)) {
                sql.append(" DESC");
            } else {
                sql.append(" ASC");
            }
        }
        
        // 添加分页
        sql.append(" LIMIT ").append(size).append(" OFFSET ").append(page * size);
        
        // 执行查询
        List<Map<String, Object>> data = jdbcTemplate.queryForList(
                sql.toString(), 
                params.toArray()
        );
        
        // 获取总记录数
        long totalCount = getTableCount(tableName, keyword);
        
        // 构建返回结果
        result.put("content", data);
        result.put("totalElements", totalCount);
        result.put("totalPages", (int) Math.ceil((double) totalCount / size));
        result.put("size", size);
        result.put("number", page);
        
        return result;
    }

    @Override
    public Map<String, Object> getRecordByPrimaryKey(String tableName, String primaryKeyName, Object primaryKeyValue) {
        String sql = "SELECT * FROM " + DATABASE_NAME + "." + escapeTableName(tableName) + 
                    " WHERE " + primaryKeyName + " = ?";
        
        List<Map<String, Object>> results = jdbcTemplate.queryForList(sql, primaryKeyValue);
        return results.isEmpty() ? null : results.get(0);
    }

    @Override
    public String getPrimaryKeyColumn(String tableName) {
        try {
            DatabaseMetaData metaData = jdbcTemplate.getDataSource().getConnection().getMetaData();
            ResultSet primaryKeys = metaData.getPrimaryKeys(DATABASE_NAME, null, tableName);
            
            if (primaryKeys.next()) {
                return primaryKeys.getString("COLUMN_NAME");
            }
            
            // 如果没有主键，查找名为id、ID等常见主键名的列
            Map<String, String> columns = getTableColumns(tableName);
            for (String columnName : columns.keySet()) {
                if ("id".equalsIgnoreCase(columnName) || 
                    "ID".equals(columnName) || 
                    (tableName + "_id").equalsIgnoreCase(columnName)) {
                    return columnName;
                }
            }
            
            // 没有找到主键，返回第一列
            return columns.isEmpty() ? null : columns.keySet().iterator().next();
        } catch (SQLException e) {
            throw new RuntimeException("获取主键信息失败: " + e.getMessage(), e);
        }
    }

    @Override
    public long getTableCount(String tableName, String keyword) {
        StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM ")
                .append(DATABASE_NAME).append(".").append(escapeTableName(tableName));
        
        List<Object> params = new ArrayList<>();
        if (keyword != null && !keyword.isEmpty()) {
            sql.append(" WHERE ");
            
            Map<String, String> columns = getTableColumns(tableName);
            List<String> likeConditions = new ArrayList<>();
            
            for (String columnName : columns.keySet()) {
                likeConditions.add(columnName + " LIKE ?");
                params.add("%" + keyword + "%");
            }
            
            sql.append(String.join(" OR ", likeConditions));
        }
        
        return jdbcTemplate.queryForObject(sql.toString(), params.toArray(), Long.class);
    }
    
    /**
     * 转义表名，处理含有特殊字符的表名
     * 
     * @param tableName 原始表名
     * @return 转义后的表名
     */
    private String escapeTableName(String tableName) {
        return "`" + tableName.replace("`", "``") + "`";
    }
} 